<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>从MongoDB抽取数据导入mysql | 智能技术</title>
    <meta name="generator" content="VuePress 1.8.2">
    <script data-ad-client="ca-pub-1977396037355374" async="true" src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
    <meta name="description" content="从MongoDB抽取数据导入mysql示例代码">
    <meta name="baidu-site-verification" content="fP9EQkbGCM">
    
    <link rel="preload" href="/assets/css/0.styles.89695ccf.css" as="style"><link rel="preload" href="/assets/js/app.cee3c598.js" as="script"><link rel="preload" href="/assets/js/22.ea0793c3.js" as="script"><link rel="prefetch" href="/assets/js/10.81160393.js"><link rel="prefetch" href="/assets/js/11.777111f5.js"><link rel="prefetch" href="/assets/js/12.c58a9b41.js"><link rel="prefetch" href="/assets/js/13.16b64097.js"><link rel="prefetch" href="/assets/js/14.81b84b8b.js"><link rel="prefetch" href="/assets/js/15.ae9bbcfc.js"><link rel="prefetch" href="/assets/js/16.cb71ce72.js"><link rel="prefetch" href="/assets/js/17.7eb91f50.js"><link rel="prefetch" href="/assets/js/18.1f750554.js"><link rel="prefetch" href="/assets/js/19.525a894f.js"><link rel="prefetch" href="/assets/js/2.0a188998.js"><link rel="prefetch" href="/assets/js/20.f186cf8f.js"><link rel="prefetch" href="/assets/js/21.b3e01dc7.js"><link rel="prefetch" href="/assets/js/23.da81792d.js"><link rel="prefetch" href="/assets/js/24.0d075789.js"><link rel="prefetch" href="/assets/js/25.901ac41c.js"><link rel="prefetch" href="/assets/js/26.c3112033.js"><link rel="prefetch" href="/assets/js/27.0d727913.js"><link rel="prefetch" href="/assets/js/3.0d59f57e.js"><link rel="prefetch" href="/assets/js/4.58ac6eea.js"><link rel="prefetch" href="/assets/js/5.b48f4120.js"><link rel="prefetch" href="/assets/js/6.6a7eb352.js"><link rel="prefetch" href="/assets/js/7.0a07ef26.js"><link rel="prefetch" href="/assets/js/8.0332c4f5.js"><link rel="prefetch" href="/assets/js/9.9df962ab.js">
    <link rel="stylesheet" href="/assets/css/0.styles.89695ccf.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><section id="global-layout" data-v-4fb7124e><header class="header" data-v-61b62cbe data-v-4fb7124e><div class="header-navbar" data-v-61b62cbe><div class="flex-xbc main header-nav" data-v-61b62cbe><div class="nav-link" data-v-61b62cbe><!----> <nav class="link-list" data-v-61b62cbe><a href="/" class="list-item router-link-active" data-v-61b62cbe>首页</a><a href="/posts/" class="list-item" data-v-61b62cbe>博客</a><a href="/category/" class="list-item" data-v-61b62cbe>分类</a><a href="/tag/" class="list-item" data-v-61b62cbe>标签</a><a href="/about/" class="list-item" data-v-61b62cbe>关于</a></nav></div> <div class="search-box" data-v-61b62cbe><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div></div></div> </header> <!----> <section class="page" data-v-4fb7124e data-v-4fb7124e><section class="info" style="background-image:url(/images/8.jpg);" data-v-52fe94f0><article class="main info-content" data-v-a5c9dc12 data-v-52fe94f0><div class="content-header" data-v-a5c9dc12><h1 class="header-title" data-v-a5c9dc12>从MongoDB抽取数据导入mysql</h1></div> <div class="flex-wcc content-tag" data-v-a5c9dc12><div class="inblock tag-list" data-v-a5c9dc12><a href="/category/问题解决/" class="tag-text" data-v-a5c9dc12>问题解决
      </a></div> <span class="tag-space" data-v-a5c9dc12>/</span> <div class="inblock tag-list" data-v-a5c9dc12><a href="/tag/mongodb/" class="tag-text" data-v-a5c9dc12>mongodb
      </a><a href="/tag/mysql/" class="tag-text" data-v-a5c9dc12>mysql
      </a></div></div> <div class="content content__default" data-v-a5c9dc12><p>使用python代码将mongodb数据库中的数据导入mysql中，示例代码如下：</p> <div class="language-python extra-class"><pre class="language-python"><code><span class="token comment"># -*- coding: utf-8 -*-</span>
<span class="token keyword">from</span> pymongo <span class="token keyword">import</span> MongoClient
<span class="token keyword">import</span> io
<span class="token keyword">import</span> traceback
<span class="token keyword">import</span> sys
<span class="token builtin">reload</span><span class="token punctuation">(</span>sys<span class="token punctuation">)</span>
sys<span class="token punctuation">.</span>setdefaultencoding<span class="token punctuation">(</span><span class="token string">'utf8'</span><span class="token punctuation">)</span>
<span class="token keyword">import</span> MySQLdb

conn<span class="token operator">=</span>MySQLdb<span class="token punctuation">.</span>connect<span class="token punctuation">(</span>host<span class="token operator">=</span><span class="token string">'127.0.0.1'</span><span class="token punctuation">,</span>port<span class="token operator">=</span><span class="token number">3306</span><span class="token punctuation">,</span>user<span class="token operator">=</span><span class="token string">'lcc'</span><span class="token punctuation">,</span>passwd<span class="token operator">=</span><span class="token string">'chaochaoliu'</span><span class="token punctuation">,</span>db<span class="token operator">=</span><span class="token string">'weibo_casc'</span><span class="token punctuation">,</span>charset<span class="token operator">=</span><span class="token string">'utf8mb4'</span><span class="token punctuation">)</span>
cur<span class="token operator">=</span>conn<span class="token punctuation">.</span>cursor<span class="token punctuation">(</span><span class="token punctuation">)</span>

sql<span class="token operator">=</span><span class="token string">'INSERT into cas values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'</span>

</code></pre></div> <div class="language-python extra-class"><pre class="language-python"><code>client1 <span class="token operator">=</span> MongoClient<span class="token punctuation">(</span><span class="token string">'localhost'</span><span class="token punctuation">,</span> <span class="token number">1234</span><span class="token punctuation">)</span>
client2 <span class="token operator">=</span> MongoClient<span class="token punctuation">(</span><span class="token string">'localhost'</span><span class="token punctuation">,</span> <span class="token number">2341</span><span class="token punctuation">)</span>
client3 <span class="token operator">=</span> MongoClient<span class="token punctuation">(</span><span class="token string">'localhost'</span><span class="token punctuation">,</span> <span class="token number">3412</span><span class="token punctuation">)</span>
<span class="token comment">#client4 = MongoClient('localhost', 4123)</span>
client5 <span class="token operator">=</span> MongoClient<span class="token punctuation">(</span><span class="token string">'localhost'</span><span class="token punctuation">,</span> <span class="token number">5123</span><span class="token punctuation">)</span>
<span class="token comment">#client6 = MongoClient('localhost', 6123)</span>

db1 <span class="token operator">=</span> client1<span class="token punctuation">.</span>test
db2 <span class="token operator">=</span> client2<span class="token punctuation">.</span>test
db3 <span class="token operator">=</span> client3<span class="token punctuation">.</span>test
<span class="token comment">#db4 = client4.test</span>
db5 <span class="token operator">=</span> client5<span class="token punctuation">.</span>test
<span class="token comment">#db6 = client6.test</span>

users<span class="token operator">=</span>db1<span class="token punctuation">.</span>users
blogs1 <span class="token operator">=</span> db1<span class="token punctuation">.</span>microblogs
blogs2 <span class="token operator">=</span> db2<span class="token punctuation">.</span>microblogs
blogs3 <span class="token operator">=</span> db3<span class="token punctuation">.</span>microblogs
<span class="token comment">#blogs4 = db4.microblogs</span>
blogs5 <span class="token operator">=</span> db5<span class="token punctuation">.</span>microblogs
<span class="token comment">#blogs6 = db6.microblogs</span>
<span class="token comment">#blogs=[blogs1,blogs2,blogs3,blogs4,blogs5,blogs6]</span>
blogs<span class="token operator">=</span><span class="token punctuation">[</span>blogs1<span class="token punctuation">,</span>blogs2<span class="token punctuation">,</span>blogs3<span class="token punctuation">,</span>blogs5<span class="token punctuation">]</span>

<span class="token keyword">for</span> data <span class="token keyword">in</span> users<span class="token punctuation">.</span>find<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
	<span class="token keyword">try</span><span class="token punctuation">:</span>
		userid<span class="token operator">=</span>data<span class="token punctuation">[</span><span class="token string">'id'</span><span class="token punctuation">]</span>
		create_time<span class="token operator">=</span>data<span class="token punctuation">[</span><span class="token string">'created_at'</span><span class="token punctuation">]</span>
		location<span class="token operator">=</span>data<span class="token punctuation">[</span><span class="token string">'location'</span><span class="token punctuation">]</span>
		<span class="token keyword">for</span> b <span class="token keyword">in</span> blogs<span class="token punctuation">:</span>
			param<span class="token operator">=</span><span class="token punctuation">[</span><span class="token punctuation">]</span>
			<span class="token keyword">try</span><span class="token punctuation">:</span>
				<span class="token keyword">for</span> weibo <span class="token keyword">in</span> b<span class="token punctuation">.</span>find<span class="token punctuation">(</span><span class="token punctuation">{</span><span class="token string">&quot;user_id&quot;</span><span class="token punctuation">:</span>userid<span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
					<span class="token keyword">try</span><span class="token punctuation">:</span>
						<span class="token builtin">id</span><span class="token operator">=</span>weibo<span class="token punctuation">[</span><span class="token string">'_id'</span><span class="token punctuation">]</span>
						text<span class="token operator">=</span>weibo<span class="token punctuation">[</span><span class="token string">'text'</span><span class="token punctuation">]</span>
						time<span class="token operator">=</span>weibo<span class="token punctuation">[</span><span class="token string">'created_at'</span><span class="token punctuation">]</span>
						<span class="token keyword">if</span> weibo<span class="token punctuation">.</span>has_key<span class="token punctuation">(</span><span class="token string">'retweeted_status'</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
							ret<span class="token operator">=</span><span class="token string">&quot;1&quot;</span>
							re_stat<span class="token operator">=</span>weibo<span class="token punctuation">[</span><span class="token string">'retweeted_status'</span><span class="token punctuation">]</span>
							re_text<span class="token operator">=</span>re_stat<span class="token punctuation">[</span><span class="token string">'text'</span><span class="token punctuation">]</span>
							re_time<span class="token operator">=</span>re_stat<span class="token punctuation">[</span><span class="token string">'created_at'</span><span class="token punctuation">]</span>
							re_user<span class="token operator">=</span>re_stat<span class="token punctuation">[</span><span class="token string">'user_id'</span><span class="token punctuation">]</span>
						<span class="token keyword">else</span><span class="token punctuation">:</span>
							ret<span class="token operator">=</span><span class="token string">&quot;0&quot;</span>
							re_text<span class="token operator">=</span><span class="token string">&quot;&quot;</span>
							re_time<span class="token operator">=</span><span class="token string">&quot;&quot;</span>
							re_user<span class="token operator">=</span><span class="token string">&quot;&quot;</span>
						param<span class="token punctuation">.</span>append<span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token builtin">str</span><span class="token punctuation">(</span><span class="token builtin">id</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>userid<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>text<span class="token punctuation">.</span>encode<span class="token punctuation">(</span><span class="token string">&quot;utf8&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>time<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>location<span class="token punctuation">.</span>encode<span class="token punctuation">(</span><span class="token string">&quot;utf8&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>create_time<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>re_text<span class="token punctuation">.</span>encode<span class="token punctuation">(</span><span class="token string">&quot;utf8&quot;</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>re_time<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>re_user<span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token builtin">str</span><span class="token punctuation">(</span>ret<span class="token punctuation">)</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
					<span class="token keyword">except</span><span class="token punctuation">:</span>
						<span class="token keyword">continue</span>
				<span class="token keyword">try</span><span class="token punctuation">:</span>
					cur<span class="token punctuation">.</span>executemany<span class="token punctuation">(</span>sql<span class="token punctuation">,</span>param<span class="token punctuation">)</span>
					conn<span class="token punctuation">.</span>commit<span class="token punctuation">(</span><span class="token punctuation">)</span>
				<span class="token keyword">except</span><span class="token punctuation">:</span>
					<span class="token keyword">continue</span>
			<span class="token keyword">except</span><span class="token punctuation">:</span>
				<span class="token keyword">continue</span>
	<span class="token keyword">except</span><span class="token punctuation">:</span>
		<span class="token keyword">continue</span>

cur<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
conn<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
client1<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
client2<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
client3<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
client4<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
client5<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
client6<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>


</code></pre></div></div> <div class="content-time" data-v-a5c9dc12><time datetime="2017年12月28日" class="time-text" data-v-a5c9dc12>Create Time: 2017年12月28日
    </time> <time datetime="2020年9月13日" class="time-text" data-v-a5c9dc12>Last Updated: 2020年9月13日
    </time></div></article> <section class="flex-xb main info-nav" data-v-e08c9474 data-v-52fe94f0><a href="/2018/11/30/joint-modeling-of-event-sequence-and-time-series-with-attentional%E8%AE%BA%E6%96%87%E7%AC%94%E8%AE%B0/" class="flex-xb nav-item" data-v-e08c9474><div class="flex-xcc item-img" data-v-e08c9474><img data-src="/images/3.jpg" loading="lazy" alt="Joint Modeling of Event Sequence and Time Series with Attentional论文笔记" class="img lazy" data-v-e08c9474></div> <article class="flex-ysc item-content" data-v-e08c9474><h2 class="content-title" data-v-e08c9474>Joint Modeling of Event Sequence and Time Series with Attentional论文笔记</h2> <div class="content" data-v-e08c9474></div></article></a> <a href="/2017/12/27/hdfs%E6%95%B0%E6%8D%AE%E7%94%A8mapreduce%E5%AF%BC%E5%85%A5hbase/" class="flex-xb nav-item" data-v-e08c9474><div class="flex-xcc item-img" data-v-e08c9474><img data-src="/images/2.jpg" loading="lazy" alt="HDFS数据用MapReduce导入Hbase" class="img lazy" data-v-e08c9474></div> <article class="flex-ysc item-content" data-v-e08c9474><h2 class="content-title" data-v-e08c9474>HDFS数据用MapReduce导入Hbase</h2> <div class="content" data-v-e08c9474></div></article></a></section> <!----></section></section> <footer class="footer" data-v-5bc4f524 data-v-4fb7124e><nav class="link-list" data-v-5bc4f524><a href="https://gitee.com/tjuchaochao" target="_blank" rel="noopener noreferrer" class="list-item" data-v-5bc4f524>gitee</a></nav> <a href="/" class="copyright router-link-active" data-v-5bc4f524>智能技术 © 2021</a></footer></section><div class="global-ui"><!----><!----></div></div>
    <script src="/assets/js/app.cee3c598.js" defer></script><script src="/assets/js/22.ea0793c3.js" defer></script>
  </body>
</html>
